﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

using Microsoft.Office.Interop.Excel;

namespace LotteryText {
    class ExcelHelper {
        [System.Runtime.InteropServices.DllImport("shell32.dll")]
        private static extern int ShellExecute(IntPtr hwnd, StringBuilder lpszOp, StringBuilder lpszFile, StringBuilder lpszParams, StringBuilder lpszDir, int FsShowCmd);

        public static List<List<string>> LoadT1(string fileName) {
            var result = new List<List<String>>();

            // 创建Excel
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            var r = ws.get_Range(ws.Cells[6, 2], ws.Cells[7, 15]);
            var d = r.Value2 as object[,];

            var rowCount = d.Length / 14;
            for (int i = 1; i <= rowCount; i++) {
                var rowLine = new List<string>();
                for (int j = 1; j <= 14; j++) {
                    var c = d[i, j] == null ? string.Empty : d[i, j].ToString();
                    rowLine.Add(c);
                }
                result.Add(rowLine);
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }

        public static List<List<string>> LoadT2(string fileName) {
            var result = new List<List<string>>();

            // 创建Excel
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            var row = 6;
            var rowOffset = 0;
            while (true) {
                var cell = ws.Cells[row, 1] as Range;
                if (string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    rowOffset++;
                }

                row++;
            }

            var COL_COUNT = 1 + 14;
            if (rowOffset > 0) {
                var r = ws.get_Range(ws.Cells[6, 1], ws.Cells[5 + rowOffset, COL_COUNT]);
                var d = r.Value2 as object[,];

                var rowCount = d.Length / COL_COUNT;
                for (int i = 1; i <= rowCount; i++) {
                    var rowLine = new List<string>();
                    for (int j = 1; j <= COL_COUNT; j++) {
                        var c = d[i, j] == null ? string.Empty : d[i, j].ToString();
                        rowLine.Add(c);
                    }
                    result.Add(rowLine);
                }
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }

        /// <summary>
        /// 生成 上次预测文字分布（批量调入）
        /// </summary>
        /// <param name="qiShu">期数</param>
        /// <param name="d1">表1的数据</param>
        /// <param name="d2">表2的数据</param>
        public static void Gen(string qiShu, List<List<string>> d1, List<List<string>> d2) {
            if (d1 == null) return;
            if (d2 == null) return;

            string pathName = System.Windows.Forms.Application.StartupPath + "\\" + qiShu;
            if (!Directory.Exists(pathName))
                Directory.CreateDirectory(pathName);

            // cc: 场次
            for (int cc = 5; cc < 14; cc++) {
                string fileName = string.Format(pathName + "\\第 {0} 期：最大{1}场：上次预测文字分布（批量调入）.xlsx",
                    qiShu, cc);
                var data = new List<List<string>>();
                var line1 = new List<string>();
                line1.Add(string.Format("本次：最大{0}场场次号", cc));
                var tempLine1 = new List<int>();
                for (int i = 0; i < cc; i++) {
                    tempLine1.Add(Convert.ToInt16(d1[0][i]));
                }
                tempLine1 = (from item in tempLine1
                             orderby item ascending
                             select item).ToList();
                for (int i = 0; i < 14; i++) {
                    if (tempLine1.IndexOf(i + 1) != -1)
                        line1.Add((i + 1).ToString());
                    else
                        line1.Add(string.Empty);
                }
                data.Add(line1);

                var line2 = new List<string>();
                line2.Add(string.Format("上次：最大{0}场场次号", cc));
                var tempLine2 = new List<int>();
                for (int i = 0; i < cc; i++) {
                    tempLine2.Add(Convert.ToInt16(d1[1][i]));
                }
                tempLine2 = (from item in tempLine2 orderby item ascending select item).ToList();
                for (int i = 0; i < 14; i++) {
                    if (tempLine2.IndexOf(i + 1) != -1)
                        line2.Add((i + 1).ToString());
                    else
                        line2.Add(string.Empty);
                }
                data.Add(line2);
                var line3 = new List<string>();
                line3.Add("对齐：以本次场次号为准");
                var countLine3 = 0;
                for (int i = 1; i < 15; i++) {
                    if (countLine3 < cc) {
                        var item = line1[i];
                        if (!string.IsNullOrEmpty(item)) {
                            line3.Add(tempLine2[countLine3++].ToString());
                        } else {
                            line3.Add(string.Empty);
                        }
                    } else {
                        line3.Add(string.Empty);
                    }
                }
                data.Add(line3);

                for (int i = 0; i < d2.Count; i++) {
                    var line = new List<string>();
                    line.Add(d2[i][0]); // 序号

                    var firstItem = d2[i][1];
                    var token = string.Empty;
                    if (firstItem == "大" || firstItem == "中" || firstItem == "小") {
                        token = "大";
                    } else {
                        token = "首";
                    }

                    var count = 0;
                    for (int j = 1; j < 15; j++) {
                        if (count < cc) {
                            var item = line1[j];
                            if (!string.IsNullOrEmpty(item)) {
                                var indx = tempLine2[count++];
                                line.Add(d2[i][indx]);
                            } else {
                                line.Add(token);
                            }
                        } else {
                            line.Add(token);
                        }
                    }
                    data.Add(line);
                }

                // 创建Excel
                var app = new ApplicationClass();
                app.Visible = false;
                app.UserName = " ";

                //增加工作表
                app.Workbooks.Add(Type.Missing);
                //删除多余的两个Sheet
                app.ActiveWindow.SelectedSheets.Delete();
                app.ActiveWindow.SelectedSheets.Delete();
                app.WindowState = XlWindowState.xlMaximized;

                // 显示网格线
                app.ActiveWindow.DisplayGridlines = true;

                var ws = app.ActiveWorkbook.ActiveSheet as Worksheet;
                ws.Name = "预测文字分布";

                //写单元格的数据
                app.ErrorCheckingOptions.BackgroundChecking = false;
                var lineCount = data[0].Count;
                var rowCount = data.Count;

                // 设置网格线
                Range rs = null;

                rs = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, lineCount]);
                rs.Merge(true);
                rs.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                rs.VerticalAlignment = XlVAlign.xlVAlignCenter;
                ws.Cells[1, 1] = string.Format("第 {0}  期：上次预测文字：最大{1}场：预测文字",
                    qiShu, cc);

                rs = ws.get_Range(ws.Cells[2, 1], ws.Cells[5, 1]);
                rs.Merge(Type.Missing);
                rs.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rs.VerticalAlignment = XlVAlign.xlVAlignCenter;
                ws.Cells[2, 1] = "序号";
                rs.ColumnWidth = 25;

                rs = ws.get_Range(ws.Cells[2, 2], ws.Cells[4, 15]);
                rs.Merge(Type.Missing);
                rs.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rs.VerticalAlignment = XlVAlign.xlVAlignCenter;
                ws.Cells[2, 2] = "预测文字分布";
                for (int i = 2; i < 16; i++) {
                    rs = ws.get_Range(ws.Cells[5, i], ws.Cells[5, i]);
                    rs.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    rs.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    ws.Cells[5, i] = i - 1;
                    rs.ColumnWidth = 3;
                    rs.Font.Bold = true;
                }

                rs = ws.get_Range(ws.Cells[2, 1], ws.Cells[5 + rowCount, lineCount]);
                rs.Borders.LineStyle = XlLineStyle.xlContinuous;
                rs.Borders.Weight = XlBorderWeight.xlThin;

                // 把单元格格式位置为文本
                rs.NumberFormatLocal = "@";

                rs = ws.get_Range(ws.Cells[6, 1], ws.Cells[5 + rowCount, lineCount]);
                rs.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rs.VerticalAlignment = XlVAlign.xlVAlignCenter;
                var r = rowCount;
                var c = lineCount;
                var v = new string[r, c];
                for (int i = 0; i < r; i++) {
                    for (int j = 0; j < c; j++) {
                        v[i, j] = data[i][j];
                    }
                }
                rs.Value2 = v;


                //保存文件
                app.DisplayAlerts = false;
                app.ActiveWorkbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;

            }

            // 打开生成的文件夹
            ShellExecute(IntPtr.Zero, new StringBuilder("Open"), new StringBuilder(pathName), new StringBuilder(""), new StringBuilder(""), 1);
        }
    }
}
